﻿/*--  lcheng  2011.06.29   YorkCMS_信息点击记录 创建表 */
IF OBJECT_ID('cms_log_ContentClickLog') IS NOT NULL
BEGIN
    DROP TABLE cms_log_ContentClickLog
    PRINT '<<< DROPPED TABLE cms_log_ContentClickLog >>>'
END
go
CREATE TABLE cms_log_ContentClickLog(
    iLogId               int              IDENTITY(1,1),
    dtClickTime          datetime         DEFAULT getdate() NOT NULL,
    iForegroundUserId    int              DEFAULT 0 NULL,
    nvcIP                nvarchar(20)     NULL,
    nvcReferenceURL      nvarchar(200)    NULL,
    iContentID           int              DEFAULT 0 NOT NULL
)
go
IF OBJECT_ID('cms_log_ContentClickLog') IS NOT NULL
    PRINT '<<< CREATED TABLE cms_log_ContentClickLog >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE cms_log_ContentClickLog >>>'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', default, default))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog'
END
exec sys.sp_addextendedproperty 'MS_Description', '信息点击记录表', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iLogId'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iLogId'
END
exec sys.sp_addextendedproperty 'MS_Description', '记录ID', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iLogId'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'dtClickTime'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'dtClickTime'
END
exec sys.sp_addextendedproperty 'MS_Description', '添加时间', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'dtClickTime'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iForegroundUserId'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iForegroundUserId'
END
exec sys.sp_addextendedproperty 'MS_Description', '前台登陆的用户Id', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iForegroundUserId'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'nvcIP'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'nvcIP'
END
exec sys.sp_addextendedproperty 'MS_Description', 'IP', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'nvcIP'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'nvcReferenceURL'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'nvcReferenceURL'
END
exec sys.sp_addextendedproperty 'MS_Description', '来源URL', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'nvcReferenceURL'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iContentID'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iContentID'
END
exec sys.sp_addextendedproperty 'MS_Description', '信息ID', 'schema', 'dbo', 'table', 'cms_log_ContentClickLog', 'column', 'iContentID'
go
ALTER TABLE cms_log_ContentClickLog ADD 
    CONSTRAINT PK_cms_log_ContentClickLog PRIMARY KEY NONCLUSTERED (iLogId)
go
/* lcheng   2011.06.29    YorkCMS_信息点击记录 创建存储过程*/
IF OBJECT_ID('cms_log_ContentClickLog') IS NOT NULL
    PRINT '<<< CREATED TABLE cms_log_ContentClickLog >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE cms_log_ContentClickLog >>>'
go
IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='cms_ContentClickLog_Save' AND ROUTINE_TYPE='PROCEDURE')
DROP PROCEDURE cms_ContentClickLog_Save
GO 
CREATE PROCEDURE cms_ContentClickLog_Save
@iLogId    	int OUTPUT,
@dtClickTime    	datetime,
@iForegroundUserId    	int,
@nvcIP    	nvarchar(20),
@nvcReferenceURL    	nvarchar(200),
@iContentID    	int
AS
IF (@iLogId>0) 
BEGIN
	UPDATE cms_log_ContentClickLog
	SET 
		[dtClickTime] = @dtClickTime,
		[iForegroundUserId] = @iForegroundUserId,
		[nvcIP] = @nvcIP,
		[nvcReferenceURL] = @nvcReferenceURL,
		[iContentID] = @iContentID
	WHERE [iLogId]=@iLogId
END
ELSE
BEGIN
	INSERT INTO cms_log_ContentClickLog([dtClickTime],[iForegroundUserId],[nvcIP],[nvcReferenceURL],[iContentID])
	VALUES(@dtClickTime,@iForegroundUserId,@nvcIP,@nvcReferenceURL,@iContentID)
	SET @iLogId = @@IDENTITY
END
GO
/*lcheng  2011.06.30   YorkCMS_信息点击记录 创建视图 cms_vwContentClickLog */
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[cms_vwContentClickLog]'))
DROP VIEW [cms_vwContentClickLog]
GO

CREATE VIEW [cms_vwContentClickLog]
AS
SELECT     cms_log_ContentClickLog.iLogId, cms_content_Content.ContentId,  cms_content_Content.Name AS ContentName,cms_log_ContentClickLog.dtClickTime, 
           cms_content_Content.AddUserId,  cms_content_Content.PubTime,   cms_content_Content.PubStatus, 
           cms_content_Content.NodeId,     cms_content_Content.TopWeight, cms_content_Content.EliteWeight, 
           cms_content_Content.DisplayOrder
FROM         cms_content_Content INNER JOIN
             cms_log_ContentClickLog ON cms_content_Content.ContentId = cms_log_ContentClickLog.iContentID 
WHERE     (cms_content_Content.PubStatus = 1)
GO

/* lcheng   2011.06.30   YorkCMS_信息点击记录 创建视图 cms_vwContentBaseInfoForClick */
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[cms_vwContentBaseInfoForClick]'))
DROP VIEW cms_vwContentBaseInfoForClick
GO

CREATE VIEW cms_vwContentBaseInfoForClick
AS

SELECT     cms_content_Content.ContentId, cms_content_Content.Name AS ContentName, cms_content_Content.Url, cms_content_Content.AddUserId, 
           cms_content_Content.PubTime,cms_content_Content.AddTime ,cms_content_Content.PubStatus, cms_content_Content.NodeId, cms_node_Node.Name AS NodeName, 
           cms_user_AdminUsers.Name AS AdminUserName, cms_user_AdminUsers.LogName AS AdminUserLogName, cms_content_Content.TopWeight, 
           cms_content_Content.EliteWeight, cms_content_Content.DisplayOrder
FROM         cms_content_Content INNER JOIN
             cms_node_Node ON cms_content_Content.NodeId = cms_node_Node.NodeId INNER JOIN
             cms_user_AdminUsers ON cms_content_Content.AddUserId = cms_user_AdminUsers.UserBaseId
WHERE     (cms_content_Content.PubStatus = 1)
GO
/*lcheng 2011.06.30   YorkCMS_信息点击记录 添加信息统计插件 */
SET IDENTITY_INSERT [cms_user_UserPermission] ON
INSERT INTO [cms_user_UserPermission] (UserRightCodeId,[ParentId] ,[KeyWord] ,[Description]) VALUES ( 11,4,'Plugin_contentclick', '信息统计')
SET IDENTITY_INSERT [cms_user_UserPermission] OFF
/*Lcheng 2011.08.10 YorkCMS_模板在线编辑插件 */
SET IDENTITY_INSERT [cms_user_UserPermission] ON
INSERT INTO [cms_user_UserPermission] (UserRightCodeId,[ParentId] ,[KeyWord] ,[Description]) VALUES ( 12,4,'plugin_TemplateControl', '模板在线编辑')
SET IDENTITY_INSERT [cms_user_UserPermission] OFF
/* 编辑器修改 */
update cms_model_FormInputType set Name='HtmlEditor' ,Description='内容编辑器，默认ckeditor' where FormInputTypeId=7
/*Lcheng 2011.08.10 YorkCMS_评论记录 */
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[cms_vwCommendAllInfo]'))
DROP VIEW cms_vwContentBaseInfoForClick
GO

CREATE VIEW cms_vwCommendAllInfo
AS

SELECT cms_rec_Recom.RecommendId,cms_rec_Recom.Name,cms_rec_Recom.Detail,
       cms_rec_RecomContent.ContentId,cms_rec_RecomContent.IP,cms_rec_RecomContent.IsAnonymity,cms_rec_RecomContent.IsAudit,
       cms_rec_RecomContent.UserName,cms_rec_RecomContent.AddUserId,cms_rec_RecomContent.AddTime
FROM cms_rec_Recom , cms_rec_RecomContent
where cms_rec_Recom.RecommendId=cms_rec_RecomContent.RecomId 

GO

/* YorkCMS_基本信息 创建视图 cms_vwAllBaseInfo */
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[cms_vwAllBaseInfo]'))
DROP VIEW cms_vwAllBaseInfo
GO

CREATE VIEW cms_vwAllBaseInfo
AS

SELECT [ContentId] ,[NodeId] ,[Name] ,[Url] ,[TopWeight] ,[EliteWeight]  ,[DisplayOrder] ,[PubUserId]      ,[PubTime]      ,[Click]
FROM [cms_content_Content]
WHERE PubStatus=1  

GO

/* 多线程发布 */

CREATE TABLE cms_pub_pubTask(
    intPubTaskId     int               IDENTITY(1,1),
    dtAddTime        datetime          DEFAULT getdate() NOT NULL,
    intTaskStatus    int               NULL,
    intAdminID       int               DEFAULT 0 NULL,
    nvcActiontype    nvarchar(50)      NULL,
    intNodeID        int               NULL,
    nvcNodeName      nvarchar(200)     NULL,
    nvcContentIDs    nvarchar(2000)    NULL,
    intIndexPage     int               NULL,
    intCountNum      int               NULL
)
go
IF OBJECT_ID('cms_pub_pubTask') IS NOT NULL
    PRINT '<<< CREATED TABLE cms_pub_pubTask >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE cms_pub_pubTask >>>'
go

if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', default, default))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask'
END
exec sys.sp_addextendedproperty 'MS_Description', '发布任务表', 'schema', 'dbo', 'table', 'cms_pub_pubTask'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intPubTaskId'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intPubTaskId'
END
exec sys.sp_addextendedproperty 'MS_Description', '任务id', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intPubTaskId'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'dtAddTime'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'dtAddTime'
END
exec sys.sp_addextendedproperty 'MS_Description', '添加时间', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'dtAddTime'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intTaskStatus'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intTaskStatus'
END
exec sys.sp_addextendedproperty 'MS_Description', '执行状态', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intTaskStatus'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intAdminID'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intAdminID'
END
exec sys.sp_addextendedproperty 'MS_Description', '操作人员ID', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intAdminID'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcActiontype'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcActiontype'
END
exec sys.sp_addextendedproperty 'MS_Description', '操作类型Key', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcActiontype'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intNodeID'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intNodeID'
END
exec sys.sp_addextendedproperty 'MS_Description', '操作的结点', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intNodeID'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcNodeName'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcNodeName'
END
exec sys.sp_addextendedproperty 'MS_Description', '操作的结点名称', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcNodeName'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcContentIDs'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcContentIDs'
END
exec sys.sp_addextendedproperty 'MS_Description', '操作的内容IDs', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'nvcContentIDs'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intIndexPage'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intIndexPage'
END
exec sys.sp_addextendedproperty 'MS_Description', '列表页起始页次', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intIndexPage'
go
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intCountNum'))
BEGIN
    exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intCountNum'
END
exec sys.sp_addextendedproperty 'MS_Description', '列表页页面数量', 'schema', 'dbo', 'table', 'cms_pub_pubTask', 'column', 'intCountNum'
go
ALTER TABLE cms_pub_pubTask ADD 
    CONSTRAINT PK_cms_pub_pubTask PRIMARY KEY NONCLUSTERED (intPubTaskId)
go

IF OBJECT_ID('cms_pub_pubTask') IS NOT NULL
    PRINT '<<< CREATED TABLE cms_pub_pubTask >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE cms_pub_pubTask >>>'
go
/* 保存 Save cms_pubTask */
IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='cms_pubTask_Save' AND ROUTINE_TYPE='PROCEDURE')
DROP PROCEDURE cms_pubTask_Save
GO 
CREATE PROCEDURE cms_pubTask_Save
@intPubTaskId    	int OUTPUT,
@dtAddTime    	datetime,
@intTaskStatus    	int,
@intAdminID    	int,
@nvcActiontype    	nvarchar(50),
@intNodeID    	int,
@nvcNodeName    	nvarchar(200),
@nvcContentIDs    	nvarchar(2000),
@intIndexPage    	int,
@intCountNum    	int
AS
IF (@intPubTaskId>0) 
BEGIN
	UPDATE cms_pub_pubTask
	SET 
		[dtAddTime] = @dtAddTime,
		[intTaskStatus] = @intTaskStatus,
		[intAdminID] = @intAdminID,
		[nvcActiontype] = @nvcActiontype,
		[intNodeID] = @intNodeID,
		[nvcNodeName] = @nvcNodeName,
		[nvcContentIDs] = @nvcContentIDs,
		[intIndexPage] = @intIndexPage,
		[intCountNum] = @intCountNum
	WHERE [intPubTaskId]=@intPubTaskId
END
ELSE
BEGIN
	INSERT INTO cms_pub_pubTask([dtAddTime],[intTaskStatus],[intAdminID],[nvcActiontype],[intNodeID],[nvcNodeName],[nvcContentIDs],[intIndexPage],[intCountNum])
	VALUES(@dtAddTime,@intTaskStatus,@intAdminID,@nvcActiontype,@intNodeID,@nvcNodeName,@nvcContentIDs,@intIndexPage,@intCountNum)
	SET @intPubTaskId = @@IDENTITY
END
GO


/* 附件的辅助信息 */
ALTER TABLE cms_content_ContentUpFile ADD
	FileURL nvarchar(1000) NULL
GO
UPDATE cms_content_ContentUpFile SET FileURL=''
/* 保存 Save cms_ContentUpFile */
IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='cms_ContentUpFile_Save' AND ROUTINE_TYPE='PROCEDURE')
DROP PROCEDURE cms_ContentUpFile_Save
GO 
CREATE PROCEDURE cms_ContentUpFile_Save
@ContentUpFileId    	int OUTPUT,
@NodeID    	int,
@ContentId    	int,
@FileRoot    	nvarchar(500),
@Category    	varchar(100),
@AttributeID    	int,
@AddTime    	datetime,
@AddUserId    	int,
@FileSize    	decimal,
@FileURL    	nvarchar(1000)
AS
IF (@ContentUpFileId>0) 
BEGIN
	UPDATE cms_content_ContentUpFile
	SET 
		[NodeID] = @NodeID,
		[ContentId] = @ContentId,
		[FileRoot] = @FileRoot,
		[Category] = @Category,
		[AttributeID] = @AttributeID,
		[AddTime] = @AddTime,
		[AddUserId] = @AddUserId,
		[FileSize] = @FileSize,
		[FileURL] = @FileURL
	WHERE [ContentUpFileId]=@ContentUpFileId
END
ELSE
BEGIN
	INSERT INTO cms_content_ContentUpFile([NodeID],[ContentId],[FileRoot],[Category],[AttributeID],[AddTime],[AddUserId],[FileSize],[FileURL])
	VALUES(@NodeID,@ContentId,@FileRoot,@Category,@AttributeID,@AddTime,@AddUserId,@FileSize,@FileURL)
	SET @ContentUpFileId = @@IDENTITY
END
GO
-- V1.1.0.2 结束
-- V3.0.0.0 没有sql